package model;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class CommentModel {

    Connection connection;

    public CommentModel(Connection connection) {
        this.connection = connection;
    }

    public int insertComment(int usernameId, int postId, String content) throws SQLException {

        PostModel postModel = new PostModel(connection);
        UserModel userModel = new UserModel(connection);
        AchievementModel achievementModel = new AchievementModel(connection);

        String query = "INSERT INTO comment (username_id, post_id, datetime, content) "
                + "VALUES ('" + usernameId + "', '" + postId + "', NOW(), '" + content + "')";

        Statement statement = connection.createStatement();
        String[] keyColumn = {"ID"};
        statement.executeUpdate(query, keyColumn);
        ResultSet r = statement.getGeneratedKeys();
        r.next();
        int new_id = r.getInt(1);
        System.out.println("NEW : " + new_id);
            
        postModel.incPostCommentCount(postId);
        userModel.incCommentCount(postId + "");

        //otomatis dapet achievement pertama
        achievementModel.addAchievementToUser(usernameId + "", achievementModel.COMMENT_ONCE);

        //jika diatas 10 kali                
        ResultSet res = userModel.getUserInfo(usernameId + "");
        res.next();
        if (res.getInt("comment_count") >= 20) {
            achievementModel.addAchievementToUser(usernameId + "", achievementModel.COMMENT_20);
        }

        return new_id;
    }

    public ResultSet getAllComment(int post_id) throws SQLException {
        String query = "SELECT comment.ID AS comment_id,username_id, user.username AS username, comment.datetime AS datetime, comment.content AS content, avatar "
                + " FROM comment INNER JOIN user ON user.ID= comment.username_id "
                + " WHERE comment.post_id=" + post_id
                + " ORDER BY comment.datetime DESC";

        Statement statement = connection.createStatement();
        ResultSet res = statement.executeQuery(query);
        return res;
    }

    public ResultSet getComment(int comment_id) throws SQLException {
        String query = "SELECT comment.ID AS comment_ID, username_id, user.username AS username, comment.datetime AS datetime, comment.content AS content, avatar, post_id "
                + "FROM comment "
                + "INNER JOIN user ON user.ID= comment.username_id "
                + "WHERE comment.ID=$comment_id LIMIT 1";

        Statement statement = connection.createStatement();
        return statement.executeQuery(query);
    }

    public boolean delComment(int comment_id) throws SQLException {
        ResultSet comment_res = getComment(comment_id);
        boolean del = false;
        while (comment_res.next()) {
            PostModel post_model = new PostModel(connection);
            UserModel user_model = new UserModel(connection);

            post_model.decPostCommentCount(comment_res.getInt("post_id"));
            user_model.decCommentCount(comment_res.getString("username_id"));

            String query = "DELETE FROM comment WHERE ID=" + comment_id;
            
            Statement statement = connection.createStatement();
            statement.executeUpdate(query);
            
            del = true;
        }
         return del;        
    }
}
